package org.nf.db.util;

import org.nf.db.util.exception.CloseResourceException;
import org.nf.db.util.exception.DataAccessException;
import org.nf.db.util.exception.ExecuteParamsException;
import org.nf.db.util.exception.TransactionException;

import javax.sql.DataSource;
import java.sql.*;

/**
 * @author wangl
 * @date 2021/7/26
 * SQl执行器，也是框架的核心入口类
 */
public class SqlExecutor {

    /**
     * 连接对象，由使用者从外部传入
     */
    private final Connection connection;

    /**
     * 是否自动关闭
     */
    private boolean closeConn = true;

    /**
     * 从构造方法传入一个连接对象
     * @param connection 连接对象
     */
    public SqlExecutor(Connection connection) {
        this.connection = connection;
    }

    /**
     * 从数据源中获取一个连接对象
     * @param dataSource 数据源
     */
    public SqlExecutor(DataSource dataSource) {
        try {
            this.connection = dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 提供一个获取连接对象的方法
     * @return 连接对象
     */
    public Connection getConnection() {
        return this.connection;
    }

    /**
     * 开启事务
     */
    public void beginTransaction() {
        try {
            connection.setAutoCommit(false);
            closeConn = false;
        } catch (SQLException e) {
            throw new TransactionException("Begin transaction fail.", e);
        }
    }

    /**
     * 提交事务
     */
    public void commit() {
        try {
            connection.commit();
        } catch (SQLException e) {
            throw new TransactionException("Commit transaction fail.", e);
        } finally {
            close();
        }
    }

    /**
     * 回滚事务
     */
    public void rollback() {
        try {
            connection.rollback();
        } catch (SQLException e) {
            throw new TransactionException("Rollback transaction fail.", e);
        } finally {
            close();
        }
    }

    /**
     * 执行增删改操作
     * @param sql 用户定义的sql语句
     * @param params sql语句中所需要的参数，由于对sql中的参数个数是未知的，
     *               因此这里使用一个可变长度的参数类型
     */
    public int executeUpdate(String sql, Object...params) {
        if(connection == null) {
            throw new ExecuteParamsException("Null connection.");
        }
        if(sql == null) {
            close();
            throw new ExecuteParamsException("Null SQL statement.");
        }
        //声明PreparedStatement对象
        PreparedStatement ps = null;
        try {
            //预编译发送sql
            ps = connection.prepareStatement(sql);
            //设置参数
            setParameters(ps, params);
            //执行sql
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DataAccessException(e);
        } finally {
            close(ps);
            if(closeConn) {
                close();
            }
        }
    }

    /**
     * 插入记录，并返回自增长主键
     * @param sql insert语句
     * @param params 参数
     * @return 泛型对象
     */
    public <T> T insert(String sql, ResultSetHandler<T> handler, Object...params) {
        if(connection == null) {
            throw new ExecuteParamsException("Null connection.");
        }
        if(sql == null) {
            close();
            throw new ExecuteParamsException("Null SQL statement.");
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        //声明自增长主键
        T generatedKeys;
        try {
            //预编译发送sql，此时要设置一个参数，告诉PrepareStatement需要返回自增长的主键
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            setParameters(ps, params);
            //执行sql
            ps.executeUpdate();
            //调用getGeneratedKeys方法返回特殊的结果集
            rs = ps.getGeneratedKeys();
            //调用ResultSetHandler处理结果集并返回
            generatedKeys = handler.handle(rs);
        } catch(SQLException e) {
            throw new DataAccessException(e);
        } finally {
            close(rs);
            close(ps);
            if(closeConn){
                close();
            }
        }
        return generatedKeys;
    }

    /**
     * 批量操作
     * @param sql sql语句
     * @param params 批量参数
     * @return 影响行数
     */
    public int[] executeBath(String sql, Object[][] params) {
        if(connection == null) {
            throw new ExecuteParamsException("Null connection.");
        }
        if(sql == null) {
            close();
            throw new ExecuteParamsException("Null SQL statement.");
        }
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            //批量设置参数
            for (Object[] param : params) {
                setParameters(ps, param);
                //添加到批量缓存
                ps.addBatch();
            }
            //批量执行
            return ps.executeBatch();
        }catch(SQLException e) {
            throw new DataAccessException(e);
        }finally {
            close(ps);
            if(closeConn) {
                close();
            }
        }
    }

    /**
     * 设置sql语句的参数
     * @param ps PreparedStatement
     * @param params 执行参数
     */
    private void setParameters(PreparedStatement ps, Object[] params) throws SQLException{
        for(int i = 0; i < params.length; i++) {
            ps.setObject(i + 1, params[i]);
        }
    }

    /**
     * 查询
     * @param sql sql语句
     * @param handler 结果集处理器
     * @param params sql所需的参数
     * @param <T> 具体结果类型
     * @return 查询结果集
     */
    public <T> T executeQuery(String sql, ResultSetHandler<T> handler,
                              Object...params) {
        if(connection == null) {
            throw new ExecuteParamsException("Null connection.");
        }
        if(sql == null) {
            close();
            throw new ExecuteParamsException("Null SQL statement.");
        }
        if(handler == null) {
            close();
            throw new ExecuteParamsException("Null ResultSetHandler.");
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        //返回的数据
        T t = null;
        try {
            ps = connection.prepareStatement(sql);
            //设置参数
            setParameters(ps, params);
            //执行查询
            rs = ps.executeQuery();
            //将结果集传给handler处理转换
            t = handler.handle(rs);
            return t;
        } catch(SQLException e) {
            throw new DataAccessException(e);
        } finally {
            close(rs);
            close(ps);
            if(closeConn) {
                close();
            }
        }
    }

    /**
     * 关闭ResultSet
     */
    private void close(ResultSet rs) {
        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                throw new CloseResourceException("Close result set fail.", e);
            }
        }
    }

    /**
     * 关闭Statement
     */
    private void close(Statement st) {
        if(st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                throw new CloseResourceException("Close statement fail.", e);
            }
        }
    }

    /**
     * 关闭连接
     */
    private void close() {
        try {
            //如果连接不为空并且没有关闭则执行close操作
            if(connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new CloseResourceException("Close connection fail.", e);
        }
    }
}
